package scau.gsmg.misp.dao.impl;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.stereotype.Repository;

import scau.gsmg.misp.dao.BaseDAO;
import scau.gsmg.misp.dao.IStatisticsDAO;
import scau.gsmg.misp.model.SaleStatistics;

@Repository
public class StatisticsDAO extends BaseDAO implements IStatisticsDAO {

	private static final Logger logger = Logger.getLogger(StatisticsDAO.class);
	
	/**
	 * 月销售统计报表
	 */
	@Override
	public List getMonthStatistics(int year) throws Exception {
		// TODO Auto-generated method stub
		Session session = getSession();
		List dataList = new ArrayList();
		try {
			String hql = "select new Map(year(s.createTime) as year,month(s.createTime)as month, sum(s.amount) as amount, sum(s.receivable) as total) from SaleOrder s where year(s.createTime) like ? group by month(s.createTime)";
			Query query = session.createQuery(hql);
			query.setParameter(0, year);
			
			List<Map<String, Object>> list = query.list();
			if(list!=null&&list.size()>0){
			    for(Map<String, Object> map:list){
			    	SaleStatistics s = new SaleStatistics();
			    	s.setYear((Integer)map.get("year"));
					s.setMonth((Integer)map.get("month"));
					s.setAmount((Long)map.get("amount"));
					s.setTotal((double)map.get("total"));
					dataList.add(s);
			    }
			}
			return dataList;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			session.close();
		}
		return null;
	}
	
	/**
	 * 商品类别销售统计报表
	 */
	@Override
	public List getSaleStatistics(java.util.Date beginDate ,java.util.Date endDate) throws Exception {
		// TODO Auto-generated method stub
		Session session = getSession();
		List dataList = new ArrayList();
		// 把日期推迟一天才能查到包含最后一天在内的记录
		Calendar calendar = new GregorianCalendar();
		calendar.setTime(endDate);
		calendar.add(calendar.DATE, 1); // 把日期往后增加一天
		java.util.Date delate = calendar.getTime(); // 这个时间就是日期往后推一天的结果
		try {
//			String hql = "select s.goods.goodsType.name as type ,s.goods.name as name, sum(s.price) as total, sum(s.quantity) as amount from SaleOrderItems s where s.goods.goodsType.name=? and s.saleOrder.createTime between ? and ? group by s.goods.name";
			String hql = "select s.goods.goodsType.name as type ,s.goods.name as name, sum(s.price) as total, sum(s.quantity) as amount from SaleOrderItems s where s.saleOrder.createTime between ? and ? group by s.goods.name order by s.goods.goodsType.name";
//			String hql = "select s.goods.goodsType.name as type ,s.goods.name as name, sum(s.price) as total, sum(s.quantity) as amount from SaleOrderItems s where s.saleOrder.createTime >= ? and s.saleOrder.createTime <= ? group by s.goods.name order by s.goods.goodsType.name";
			Query query = session.createQuery(hql);
			
			query.setParameter(0, beginDate);
			query.setParameter(1, delate);
			
			List<Object[]> list = query.list();
			for (Object[] o : list) {
				SaleStatistics s = new SaleStatistics();
				s.setType((String)o[0]);
				s.setName((String)o[1]);
				s.setTotal((double)o[2]);
				s.setAmount((Long)o[3]);
				s.setBegin(beginDate);
				s.setEnd(endDate);
				dataList.add(s);
			}
			return dataList;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			session.close();
		}
		return null;
	}

	/**
	 * 月销售图形报表
	 */
	@Override
	public List highcharts(int year) throws Exception {
		// TODO Auto-generated method stub
		Session session = getSession();
		try {
			String hql = "select new Map(year(s.createTime) as year,month(s.createTime)as month, sum(s.amount) as amount, sum(s.receivable) as total) from SaleOrder s where year(s.createTime) like ? group by month(s.createTime)";
			Query query = session.createQuery(hql);
			query.setParameter(0, year);
			
			List<Map<String, Object>> list = query.list();
			return list;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			session.close();
		}
		return null;
	}


}
